import numpy as np
import pandas as pd
import matplotlib as mpl
import matplotlib.pyplot as plt
from functools import wraps
import cmath
import warnings
import sys

class BasicCleaning():
    ########  文件读取与保存  ##########
    # 打开并读取txt文件，返回一个str
    def open_txt(self, path, encoding='ANSI'):
        file = open(path, "r", encoding=encoding)
        content = file.read()
        file.close()
        return content
    #用于分页保存excel，data_dict{sheet_name:DataFrame}
    def save_excel_sheets(self, data_dict,save_name,index = False):
        data_list = data_dict.values()
        sheet_name_list = data_dict.keys()
        writer = pd.ExcelWriter(save_name)
        for data,sheet_name in zip(data_list,sheet_name_list):
            data.to_excel(writer,sheet_name=str(sheet_name),index=index)
        writer.save()
        writer.close()
    #用于加入后缀或前缀生成新的文件名return save_name
    def new_name(self, file_name,end_str='',start_str=''):
        name_split1 = file_name.rfind('\\')
        neme_split2 = file_name.rfind('.')
        if start_str!= '':start_str = start_str + '_'
        if end_str != '': end_str = '_' + end_str
        save_name = start_str+ file_name[name_split1+1:neme_split2]+ end_str + '.xls'
        return save_name

    ########  数据检验  ##########
    #检查column1>>column2是否存在一对多的关系
    def one_to_many_check(self, data,column1,column2):
        if column1 == column2 :
            return False#两列一样做透视表会报错，直接跳过就好
        else:
            data['num'] = 1  # 插入数值列
            data2 = pd.pivot_table(data, index=[column1, column2])  # 制作数据透视表
            data2.reset_index(inplace=True)  # 重设index
            check_frame = data2[column1].value_counts().to_frame().reset_index()
            check_frame.columns = ['关键词','对应内容个数']
            check_result =check_frame[check_frame['对应内容个数']>1]
            if check_result.shape[0]==0:
                return False
            else:
                for line in check_result.index:
                    key_word = check_result['关键词'][line]
                    relationship = check_result['对应内容个数'][line]
                    print('关键词‘{}’，存在‘{}’个对应的值，请检查并清理'.format(key_word,relationship))
                return True

    ########  数据切分  ##########
    # 拆分关键词列按逗号为两列，return data
    def column_split(self, data, column_name, split):
        data[column_name + '_1'] = None
        data[column_name + '_2'] = None
        print('正在拆分关键字，请稍等')
        for index in data.index.values:
            content = str(data[column_name][index])  # 解决部分关键字为数值的问题
            if split in content:  # 解决关键字中没有"," 的问题
                index_split = content.find(split)
                part1 = content[0: index_split]
                part2 = content[index_split + 1:]
                data[column_name + '_1'][index] = part1
                data[column_name + '_2'][index] = part2
            else:
                data[column_name + '_1'][index] = content
        print('关键字已拆分完毕\n######################################################')

        return data

    ########  查找与填充  ##########
    #实现类似vlookup的功能
    def vlookup(self, data,matching_data,left_on,right_on,fill_on,value_on,print_c=True):
        '''
        先传入原数据与匹配数据进行merge（left_on,right_on）
        再将返回值列（value_on）的内容填充到目标列（fill_on）的空值处
        :param data: frame
        :param matching_data:frame or series
        :param left_on:data的匹配列列名
        :param right_on:matching_dat的匹配列列名，注意right_on列不能有重复值
        :param fill_on:data中要填充内容的列
        :param value_on:matching_dat中提供返回值的列
        :return:填充后的数据
        '''
        if right_on != value_on:
            matching_data = matching_data[[right_on,value_on]]#取出用于匹配的数据
        matching_data.drop_duplicates(inplace=True)#去除重复行，有重复行merge后行数会增加
        if self.one_to_many_check(matching_data, right_on, value_on) == False:
            merged_data =pd.merge(data,matching_data,how='left',left_on=left_on,right_on=right_on)#利用merge函数实现vlookup功能
            null_index_before = merged_data[merged_data[fill_on].isnull() == True].index #提取要填充的列存在空值的index
            merged_data[fill_on][null_index_before]=merged_data[value_on][null_index_before] #通过数值列的结果进行填充
            null_index_after = merged_data[merged_data[fill_on].isnull() == True].index #提取填充后仍然存在空值的index
            fill_num = len(null_index_before)-len(null_index_after)#计算填充了多少个空值
            if print_c == True:
                print('通过"{}" 与"{}"匹配后，填充空值{}个，剩余空值{}个'.format(left_on,right_on,fill_num,len(null_index_after)))
            drop_list = list(matching_data.columns)
            if left_on == right_on:
                drop_list.remove(right_on)
            merged_data.drop(drop_list, axis=1, inplace=True)  # 删除多余的列
        else:print('匹配数据存在一对多的关系，请清理后重试')
        return merged_data
    #类似模糊匹配，在seek_on列中查找相应关键词并向fill_on列的nan区域填入对应的值
    def seek_and_fill(self, data,fill_on,seek_on,matching_data,seek_value,lookup=False,lookup_fill='',only_fill_mull=True):
        '''
        可以在Frame中查找某一列是否具有某些关键词，如果有这些关键词则进行填充，可以填充查找的值，也可以填充查找的值在matching_data中所对应的值
        :param data: 要进行查找与填充的Frame
        :param fill_on: 要进行填充的列名
        :param seek_on: 要进行查找的列名
        :param matching_data: 提供查找内容的Frame
        :param seek_value: matching_data中要查找的内容所在的列，会提取出来转化成list逐一遍历查找
        :param lookup: 如果填充的值就是查找的值就不用，如果填充的值是查找的值所对应另一列数据则需定位True
        :param lookup_fill: matching_data中要填入的内容所在的列
        :return: data
        '''

        if only_fill_mull == True:
            null_index_before = data[data[fill_on].isnull() == True].index  # 提取要填充的列存在空值的index
            seek_data = data[seek_on][null_index_before]#确定要筛选的范围
        else:
            null_index_before = data.index
            seek_data = data[seek_on]
        seek_list = list(matching_data[seek_value])

        for word in seek_list:
            can_fill_index = seek_data[seek_data.str.contains(word) == True].index
            data[fill_on][can_fill_index] = word

        if lookup == True:
            data_medium = data[[seek_on,fill_on]]
            data_medium = data_medium.loc[null_index_before]
            data_medium['medium'] = None
            data_medium = self.vlookup(data_medium, matching_data,fill_on,seek_value,'medium',lookup_fill,print_c = False)
            data[fill_on][null_index_before] = data_medium['medium']

        after_null_index = data[data[fill_on].isnull() == True].index# 提取要填充的列存在空值的index
        fill_num = len(null_index_before) - len(after_null_index)
        print('通过在"{}"中搜索包含"{}"的关键字进行匹配,填充空值{}个，剩余空值{}个'.format(seek_on,seek_value,fill_num,len(after_null_index)))

        return data
    #查找与填充：在seek列中搜索某个关键词，如果有，在fill列填充另一个值
    def seek_and_fill_word(self, data,fill_on,seek_on,key_word,fill_word):
        can_fill_index = data[data[seek_on].str.contains(key_word) == True].index
        data[fill_on][can_fill_index] = fill_word
        return data

    ########  根据条件返回所需的index  ##########
    #在一列中对多个关键词进行搜索，返回交集的index
    def word_seek_and(self, data,seek_word_list,seek_column):
        index_set = set()
        count = 0
        for seek_word in seek_word_list:
            word_index = data[data[seek_column].str.contains(seek_word) == True].index
            word_index_set = set(word_index)
            if count != 0:
                index_set = index_set & word_index_set
            else: index_set = word_index_set
            count += 1
        return list(index_set)
    #在一列中对多个关键词进行搜索，返回并集的index
    def word_seek_or(self, data,seek_word_list,seek_column):
        index_set = set()
        for seek_word in seek_word_list:
            word_index = data[data[seek_column].str.contains(seek_word) == True].index
            word_index_set = set(word_index)
            index_set = index_set | word_index_set
        return list(index_set)
    #在一列中对多个关键词进行搜索，返回不包含这些词的index
    def word_seek_not(self, data,seek_word_list,seek_column):
        have_index = self.word_seek_or(data, seek_word_list, seek_column)
        all_index = set(data.index)
        no_index = all_index - set(have_index)
        return list(no_index)
    #返回在那些列包含哪些关键词以及在哪些列不包含哪些关键词的index
    def contain_ornot_index(self, data,contain_column,contain_list,not_contain_column=None,not_contain_list=None):
        contain_index = set()
        for column in contain_column:
            need_index = self.word_seek_or(data,contain_list,column)
            contain_index = contain_index | set(need_index)
        if (not_contain_column!=None)and(not_contain_list!=None):
            data_contain = data.loc[list(contain_index)]
            for column in not_contain_column:
                not_need_index = self.word_seek_or(data_contain,not_contain_list,column)
                contain_index = contain_index - set(not_need_index)
        return list(contain_index)

#用于提取某列存在空值的数据,need_column不填是要所有列，填写可选取制定列
    def column_null_data(self, data,seek_column,need_column=None,null_lable = None):
        if null_lable == None:
            data_null = data[data[seek_column].isnull() == True]
        else:
            data_null_index1 = data[data[seek_column] == null_lable].index
            data_null_index2 = data[data[seek_column].isnull() == True].index
            data_null_index = list(set(data_null_index1)|set(data_null_index2 ))
            data_null = data.loc[data_null_index]
        if need_column == None:pass
        else:data_null = data_null[need_column]
        return data_null
    #用于提取某列存在空值的index
    def column_null_save(self,data,seek_column,need_column=None,save_name='无法匹配.xlsx',null_lable = None):
        if self.get_data_name(data) == '咨询':
            data_match = data[data['咨询类别A'] == "反映"]
            data_null = self.column_null_data(data_match,seek_column,need_column,null_lable = null_lable)
            null_num = data_null.shape[0]
            total_num = data_match .shape[0]
        else:
            data_null = self.column_null_data(data,seek_column,need_column,null_lable = null_lable)
            null_num = data_null.shape[0]
            total_num = data.shape[0]
            not_null_num = total_num - null_num
        if null_num == 0:
            print(seek_column + ' 列已经完全匹配，无空值')
        else:
            print(seek_column + ' 列尚有 {} 行存在空值，匹配率为 {:.3f},''未匹配数据已保存到指定文件夹'.format(null_num,not_null_num/total_num))
            data_null.to_excel(save_name)


#装饰器，检查数据处理后行数是否有所增加
    def decorat_check_line_len(func):
        @wraps(func)
        def wrapper(self,*args, **kwargs):
            def get_org_data(data_name):
                if data_name == '投诉':
                    data = self.data_compaint
                elif data_name == '举报':
                    data = self.data_report
                elif data_name == '咨询':
                     data = self.data_inquiry
                return data
            data = func(self,*args, **kwargs)
            data_len_end = data.shape[0]
            data_org = get_org_data(self.get_data_name(data))
            data_len_start = data_org.shape[0]
            rise = data_len_end - data_len_start
            if rise != 0:
                print('数据处理后增加"{}"行，匹配表中条目可能存在多对多的情况，请检查'.format(rise))
            return data
        return wrapper

    # 转变 日期列格式
    def column_to_datetime(self,data, column):
        def change(x):
            if pd.to_datetime(x) != x:
                if type(x) != str:
                    if cmath.isnan(x) == False:
                        return str(int(x))
                    else:return x
                else:return x
            else:return x
        data[column] = data[column].apply(change)  # 如果不是str则转成srt
        data[column] = pd.to_datetime(data[column])  # 转变日期格式
        data[column] = data[column].dt.date  # 只保留年月日
        return data

    # 转为datatime；并增加年、季度、月、日四列；return data
    def clean_time_split(self,data,column,to_str = False):

        data = self.column_to_datetime(data, column) # 转变日期格式

        # 提取年月日
        data['year'] = pd.DatetimeIndex(data[column]).year  # 提取年
        data['month'] = pd.DatetimeIndex(data[column]).month  # 提取月
        data['day'] = pd.DatetimeIndex(data[column]).day  # 提取日

        #按照月份确定季度
        data['season'] = None
        match_dict = {1:[1,2,3],2:[4,5,6],3:[7,8,9],4:[10,11,12]}
        for season in match_dict.keys():
            for month in match_dict[season]:
                index = data[data['month'] == month].index
                data['season'][index] = season

        if to_str != False:
            data['year'] = data['year'].apply(lambda x: str(int(x)) + '年')
            data['season'] = data['season'].apply(lambda x: str(int(x)) + '季度')
            data['month'] = data['month'].apply(lambda x: str(int(x)) + '月')
            data['day'] = data['day'].apply(lambda x: str(int(x)) + '日')

        return data